-- @owner: @zhanghuan96
-- @date: 2024/11/20
-- @testpoint: GMS_XMLGEN.GETXMLTYPE函数语法2，参数1传入cte查询语句
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_xmlgen;
create extension gms_xmlgen;
set behavior_compat_options = 'bind_procedure_searchpath';
--step1:创建表;expect:成功
drop table if exists t_getxmltype0010;
CREATE TABLE t_getxmltype0010 (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary NUMERIC(10, 2)
);
INSERT INTO t_getxmltype0010 (first_name, last_name, department_id, salary) VALUES
('John', 'Doe', 1, 50000.00),
('Jane', 'Smith', 1, 60000.00),
('Alice', 'Johnson', 2, 55000.00),
('Bob', 'Brown', 2, 70000.00),
('Charlie', 'Davis', NULL, 48000.00);

--step2:查询语句是cte查询;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('WITH department_avg_salary AS (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM
        t_getxmltype0010
    WHERE
        department_id IS NOT NULL
    GROUP BY
        department_id
)
SELECT
    d.department_id,
    d.avg_salary
FROM
    department_avg_salary d
WHERE
    d.avg_salary > 60000', 145646);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step3:清理环境;expect:成功
drop table if exists t_getxmltype0010;
drop extension if exists gms_xmlgen;